﻿Imports DTO
Imports System.Data.OleDb

Public Class LopDAO

    Public Function LayBang() As DataTable
        Dim dt As New DataTable
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT * FROM LOP"
        Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, conn)
        da.Fill(dt)
        conn.Close()
        Return dt
    End Function

    Public Function LayLop(ByVal MaLop As Integer) As LopDTO
        Dim l As New LopDTO
        Dim cn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT * FROM LOP WHERE MALOP = "
        strSQL += MaLop.ToString
        Dim cm As New OleDbCommand(strSQL, cn)
        Dim reader As OleDbDataReader = cm.ExecuteReader()
        While (reader.Read)
            l.MaLop = reader("MaLop")
            l.MaKhoi = reader("MaKhoi")
            l.SiSo = reader("SiSo")
            l.TenLop = reader("TenLop")
        End While
        Return l
    End Function


    Public Function LayDanhSach() As List(Of LopDTO)
        Dim ds As New List(Of LopDTO)
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "SELECT * FROM LOP"
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim reader As OleDbDataReader = cmd.ExecuteReader()
        While (reader.Read())
            Dim lopDTO As New LopDTO
            lopDTO.MaLop = reader("MaLop")
            lopDTO.TenLop = reader("TenLop")
            lopDTO.SiSo = Integer.Parse(reader("SiSo"))
            lopDTO.MaKhoi = Integer.Parse(reader("MaKhoi"))
            ds.Add(lopDTO)
        End While
        conn.Close()
        Return ds
    End Function

    Public Function ThemLop(ByVal lopDTO As LopDTO) As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "INSERT INTO LOP(TenLop,SiSo,MaKhoi) VALUES('" + lopDTO.TenLop + "'," + lopDTO.SiSo.ToString() + "," + lopDTO.MaKhoi.ToString() + ")"
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        Return r
    End Function


    Public Function XoaLop(ByVal lopDTO As LopDTO) As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "DELETE FROM LOP WHERE MaLop=" + lopDTO.MaLop.ToString
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        Return r
    End Function


    Public Function CapNhatLop(ByVal lopDTO As LopDTO) As Integer
        Dim conn As OleDbConnection = DataProvider.ConnectDB()
        Dim strSQL As String = "UPDATE LOP SET TenLop='" + lopDTO.TenLop + "',SiSo=" + lopDTO.SiSo.ToString() + ",MaKhoi=" + lopDTO.MaKhoi.ToString() + " WHERE MaLop=" + lopDTO.MaLop.ToString()
        Dim cmd As New OleDbCommand(strSQL, conn)
        Dim r As Integer = cmd.ExecuteNonQuery()
        conn.Close()
        Return r
    End Function

End Class
